File to H3

Turning a single small dataset into a grid of H3 hexagons.

Point Count to Hex

The following example uses a simple CSV of 311 calls in the New York City area, showing a heatmap of calls per hex 9 cell

Code

@fused.udf
def udf(
    noise_311_link: str = "https://gist.githubusercontent.com/kashuk/670a350ea1f9fc543c3f6916ab392f62/raw/4c5ced45cc94d5b00e3699dd211ad7125ee6c4d3/NYC311_noise.csv",
    res: int = 9
):
    # Load common utilities (includes duckdb helper)
    common = fused.load("https://github.com/fusedio/udfs/tree/b7637ee/public/common/")
    con = common.duckdb_connect()

    # Keep latitude and longitude (averaged per hex) alongside the hex count
    qr = f"""
    SELECT
      h3_latlng_to_cell(lat, lng, {res}) AS hex,
      COUNT(*) AS cnt,
      AVG(lat) AS lat,
      AVG(lng) AS lng
    FROM read_csv_auto('{noise_311_link}')
    WHERE lat IS NOT NULL AND lng IS NOT NULL
    GROUP BY 1
    """

    df = con.sql(qr).df()

    # Debugging: print the resulting DataFrame schema
    print(df.T)

    return df

Requirements

• Small single (< 100MB) file (GeoJSON, CSV, Parquet, etc.). In example:

lat,lng
40.7128,-74.0060
40.7128,-74.0060

• Hexagon resolution (10, 11, 12, etc.). In this example:

res: int = 9

• Field to hexagonify & Aggregation function (max 'population', avg 'income', mean 'elevation', etc.). In this example simply counting the number of calls per hex:

# Keep latitude and longitude (averaged per hex) alongside the hex count
qr = f"""
SELECT
    h3_latlng_to_cell(lat, lng, {res}) AS hex,
    COUNT(*) AS cnt,
    AVG(lat) AS lat,
    AVG(lng) AS lng
FROM read_csv_auto('{noise_311_link}')
WHERE lat IS NOT NULL AND lng IS NOT NULL
GROUP BY 1
"""

Visualize H3

In Workbench Map Viewer

• Vector H3HexagonLayer